package springboot.controller;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import springboot.bean.Euser;
import springboot.service.TuserService;

@Controller
public class ExcelController {
	@Autowired
	private TuserService tuserService;
	/**
	 * 导出Excel
	 * @param res
	 * @throws IOException
	 */
	@RequestMapping(value = "/downloadexcel", method = RequestMethod.GET)
	public void downloadexcel(HttpServletResponse res) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("信息表");
		List<Euser> eusers = tuserService.users();
		//设置导出的文件名字
		String fileName="信息表"+".xls";
		//新增数据行,并设置单元格数据
		int rowNum=1;
		//headers设置excel的第一行
		String[] headers= {"序号","姓名","地址","备注"};
		//在excel中添加表头
		HSSFRow row=sheet.createRow(0);
		for(int i=0;i<headers.length;i++) {
			HSSFCell cell=row.createCell(i);
			HSSFRichTextString text=new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		//在表中存放查询的数据放入对应的应用
		for(Euser euser: eusers) {
			HSSFRow row1=sheet.createRow(rowNum);
			row1.createCell(0).setCellValue(euser.getId());
			row1.createCell(1).setCellValue(euser.getName());
			row1.createCell(2).setCellValue(euser.getAddress());
			row1.createCell(3).setCellValue(euser.getNote());
			rowNum++;
		}
		res.setContentType("application/octet-stream");
		//new String(fileName.getBytes(),"iso-8859-1")解决中文名不显示问题
		res.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes(),"iso-8859-1"));
		res.flushBuffer();
		workbook.write(res.getOutputStream());
	}
	/**
	 * 导入Excel
	 */
	@RequestMapping(value="/uploadexcel",method = RequestMethod.POST, consumes = "multipart/form-data")
	@ResponseBody
	public Map<String, Object> uploadexcel(@RequestParam("file") MultipartFile file){
		Map<String, Object> map=new HashMap<>();
		//获取文件名
		String fileName=file.getOriginalFilename();
		List<Euser> eusers=new ArrayList<>();
		//判断文件名
		if (!fileName.matches("^.+\\.(?i)(xls)$")&&!fileName.matches("^.+\\.(?i)(xlsx)$")) {
			map.put("success", false);
			map.put("message", "上传格式不正确");
			return map;
		}
		try {
			InputStream is=file.getInputStream();
			Workbook wb=new HSSFWorkbook(is);
			Sheet sheet=wb.getSheetAt(0);
			if (sheet==null) {
				map.put("success", false);
				map.put("message", "工作簿为空");
				return map;
			}
			Euser euser;
			for(int r=1;r<=sheet.getLastRowNum();r++) {
				Row row=sheet.getRow(r);
				if (row==null) {
					continue;
				}
				euser=new Euser();
				//判断类型
//				if (row.getCell(0).getCellType()!=1) {
//					map.put("success", false);
//					map.put("message", "导入失败");
//					return map;
//				}
				String name=row.getCell(1).getStringCellValue();
				if (name==null ||name.isEmpty()) {
					map.put("success", false);
					map.put("message", "导入失败,第"+(r+1)+"行姓名未填写");
					return map;
				}
				String address=row.getCell(2).getStringCellValue();
				if (address==null ||address.isEmpty()) {
					map.put("success", false);
					map.put("message", "导入失败,第"+(r+1)+"行地址未填写");
					return map;
				}
				String note=row.getCell(3).getStringCellValue();
				euser.setName(name);
				euser.setAddress(address);
				euser.setNote(note);
				eusers.add(euser);
			}
			for(Euser user1:eusers) {
				String name=user1.getName();
				if(tuserService.excelis(name)) {
					if (tuserService.excelup(user1.getAddress(), user1.getNote(), name)) {
						map.put("success", true);
						map.put("message", "导入成功1");
					}else {
						map.put("success", false);
						map.put("message", "导入失败");
					}
				}else {
					if (tuserService.excelin(name, user1.getAddress(), user1.getNote())) {
						map.put("success", true);
						map.put("message", "导入成功2");
					}else {
						map.put("success", false);
						map.put("message", "导入失败");
					}
				}
			}
			return map;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		map.put("success", false);
		map.put("message", "导入错误");
		return map;	
	}
}
